package com.huatai.datadevelop.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.huatai.common.error.exception.ServiceException;
import com.huatai.datadevelop.constant.Constant;
import com.huatai.datadevelop.entity.ApiSuperviseEntity;
import com.huatai.datadevelop.entity.ExecuteEntity;
import com.huatai.datadevelop.entity.TableManageEntity;
import com.huatai.datadevelop.mapper.ExecuteMapper;
import com.huatai.datadevelop.utils.PageUtils;
import com.huatai.datadevelop.utils.Query;
import com.huatai.datadevelop.utils.JDBCQueryUtil;
import com.huatai.datadevelop.utils.JDBCUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * @author hecai
 * @description: TODO
 * @date 2022/12/27 14:15
 * @Version 1.0
 */
@Service
public class ExecuteService extends ServiceImpl<ExecuteMapper, ExecuteEntity> {

    @Autowired
    private TableManageService tableManageService;
    @Autowired
    private ApiSuperviseService apiSuperviseService;


    /**
     * 获取英文名称是否合规
     * @param interfaceName
     * @return
     */
    public HashMap<String,Object> getTableNameCompliance(String interfaceName){
        if(StringUtils.isEmpty(interfaceName)){
            throw new ServiceException("接口名称为空");
        }
        HashMap<String,Object> hashMap = new HashMap<>(2);
        hashMap.put("success",true);

        //判断是否全是英文
        boolean matches = interfaceName.matches("[a-zA-Z]+");
        if(!matches){
            hashMap.put("success",false);
            hashMap.put("message","只能填写英文");
            return hashMap;
        }

        QueryWrapper<ExecuteEntity> qw = new QueryWrapper<>();
        qw.eq("interface_name",interfaceName);
        long count = this.count(qw);
        if(count>0){
            hashMap.put("success",false);
            hashMap.put("message","接口名称重复，请换一个");
            return hashMap;
        }
        hashMap.put("message","验证通过");
        return hashMap;
    }

    /**
     * 分页获取数据
     *
     * @param params
     * @return
     */
    public PageUtils queryPage(Map<String, Object> params) {
        String name = (String) params.get("name");
        IPage<ExecuteEntity> page = this.page(
                new Query<ExecuteEntity>().getPage(params),
                new QueryWrapper<ExecuteEntity>().like(StringUtils.isNotBlank(name), "name", name)
        );
        return new PageUtils(page);
    }

    /**
     * 前端大屏调取数据
     * @param params
     * @param entity
     * @param paramUrl   page   limit
     * @return
     */
    public synchronized HashMap<String,Object> asyncRequestApi(Map<String, Object> params,ExecuteEntity entity,HashMap<String, Object> paramUrl){
        String type = "数据采集开放配置";
        ApiSuperviseEntity apiSuperviseEntity = new ApiSuperviseEntity();
        try{
            ApiSuperviseEntity interface_name = this.apiSuperviseService.getOne(new QueryWrapper<ApiSuperviseEntity>()
                    .eq("interface_name", entity.getInterfaceName())
                    .eq("type",type));
            if(interface_name!=null){
                apiSuperviseEntity = interface_name;
            }
        }catch (Exception ex){}

        apiSuperviseEntity.setType(type);
        apiSuperviseEntity.setInterfaceName(entity.getInterfaceName());
        apiSuperviseEntity.setName(entity.getName());
        apiSuperviseEntity.setUrl("/ht-datadevelop/api/execute/asyncRequestApi/"+entity.getInterfaceName());
        apiSuperviseEntity.setSuperviseTime(new Date());

        HashMap<String,Object> retDa = new HashMap<>();
        List<HashMap<String,Object>> retData = new ArrayList<>();
        String sqlStr = entity.getSqlStr();
        //执行sql语句
        String SQL = "select * from ( "+sqlStr+" ) as T";
        //凭借where后面的参数  EQ(=), NE(!=), GE(>=), GT(>), LE(<=), LT(<), IN(in), NIN(not in),LIKE(like)
        String sqlPJ = " 1=1";
        if(params!=null && params.size()>0){
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                if(StringUtils.isNotEmpty(sqlPJ)){
                    sqlPJ = sqlPJ + " and ";
                }
                sqlPJ = sqlPJ + JDBCQueryUtil.querySQLStr(key, value);
            }
            SQL = SQL + " where " + sqlPJ;
        }

        //内置的sql语句
        Long tableNameCount = this.getTableNameCount(sqlStr,sqlPJ);
        retDa.put("totalCount",tableNameCount);

        if(paramUrl!=null && paramUrl.size()>0){
            Integer page = Integer.parseInt(paramUrl.get("page").toString());
            Integer limit = Integer.parseInt(paramUrl.get("limit").toString());
            SQL = SQL + " limit " + (page - 1) * limit +" , " + limit + ";";
        }

        Connection connection = null;
        Statement statement = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(SQL);
            String field = entity.getField();
            String[] fieldSplit = field.split(",");
            while (resultSet.next()){
                HashMap<String,Object> hashMap = new HashMap<>(fieldSplit.length);
                for (int i = 0; i < fieldSplit.length; i++) {
                    String key = fieldSplit[i];
                    String data = resultSet.getString(i+1);
                    hashMap.put(key,data);
                }
                retData.add(hashMap);
            }
        } catch (SQLException ex) {
            apiSuperviseEntity.setStatus(Constant.ABNORMAL);
            apiSuperviseEntity.setReason(ex.getMessage());
        }finally {
            JDBCUtils.close(statement,connection);
            this.apiSuperviseService.executorService(apiSuperviseEntity);
        }
        retDa.put("dataList",retData);
        return retDa;
    }

    /**
     * 根据表名称自动装配sql
     * @param tableName
     * @return
     */
    public HashMap<String,String> getTableNameToSQL(String tableName){
        HashMap<String, String> hashMap = new HashMap<>();
        TableManageEntity tableManageEntity = this.tableManageService.getOne(new QueryWrapper<TableManageEntity>().eq("table_name", tableName));
        String field = tableManageEntity.getField();
        hashMap.put("field",field);
        String sqlStr = "SELECT "+field + " FROM "+tableName + " WHERE 1=1 ";
        hashMap.put("sqlStr",sqlStr);
        return hashMap;
    }

    /**
     * 根据表名获取数据总数
     * @param tableName
     * @return
     */
    public Long getTableNameCount(String tableName,String sqlPJ){
        Long totalCount = 0L;
        //TODO 此处还差sqlwhere之后的筛选条件未拼接
        String sql = "select count(*) from ( "+tableName+" ) as T ";
        if(StringUtils.isNotBlank(sqlPJ)){
            sql = sql + "where " + sqlPJ;
        }
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);

            while (resultSet.next()){
                totalCount = resultSet.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return totalCount;
    }
}
